-- 优化方案1：使用子查询优化（不使用CTE）
SELECT 
    sale.khdm,
    sale.je,
    sale.sl,
    risk.abnormal,
    risk.risk
FROM (
    SELECT 
        model.khdm,
        ROUND(SUM(je), 2) AS je,
        SUM(sl) AS sl 
    FROM bsyc_vm_lsxhdmx model
    GROUP BY model.khdm
) sale
LEFT JOIN (
    SELECT 
        shop_id,
        SUM(abnormal) AS abnormal,
        SUM(risk) AS risk
    FROM (
        SELECT 
            pos.shop_id,
            CASE WHEN COUNT(*) >= 2 AND COUNT(*) <= 3 THEN 1 ELSE 0 END AS abnormal,
            CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS risk
        FROM bsyc_trade_stream model
        INNER JOIN bsyc_shop_pos pos ON model.store_id = pos.store_code
        GROUP BY model.txn_reserved3, pos.shop_id, model.store_id, model.mchnt_cd
    ) risk_detail
    GROUP BY shop_id
) risk ON sale.khdm = risk.shop_id;

-- 优化方案2：使用临时表优化（适用于复杂查询）
-- 创建临时表存储销售汇总数据
SELECT 
    model.khdm,
    ROUND(SUM(je), 2) AS je,
    SUM(sl) AS sl 
INTO #temp_sale_summary
FROM bsyc_vm_lsxhdmx model
GROUP BY model.khdm;

-- 创建临时表存储风险计算数据
SELECT 
    pos.shop_id,
    CASE WHEN COUNT(*) >= 2 AND COUNT(*) <= 3 THEN 1 ELSE 0 END AS abnormal,
    CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS risk
INTO #temp_risk_detail
FROM bsyc_trade_stream model
INNER JOIN bsyc_shop_pos pos ON model.store_id = pos.store_code
GROUP BY model.txn_reserved3, pos.shop_id, model.store_id, model.mchnt_cd;

-- 创建临时表存储风险汇总数据
SELECT 
    shop_id,
    SUM(abnormal) AS abnormal,
    SUM(risk) AS risk
INTO #temp_risk_summary
FROM #temp_risk_detail
GROUP BY shop_id;

-- 最终查询
SELECT 
    sale.khdm,
    sale.je,
    sale.sl,
    risk.abnormal,
    risk.risk
FROM #temp_sale_summary sale
LEFT JOIN #temp_risk_summary risk ON sale.khdm = risk.shop_id;

-- 清理临时表
DROP TABLE #temp_sale_summary;
DROP TABLE #temp_risk_detail;
DROP TABLE #temp_risk_summary;

-- 优化方案3：使用表变量优化（适用于中等数据量）
DECLARE @sale_summary TABLE (
    khdm VARCHAR(50),
    je DECIMAL(15,2),
    sl INT
);

DECLARE @risk_summary TABLE (
    shop_id VARCHAR(50),
    abnormal INT,
    risk INT
);

-- 插入销售汇总数据
INSERT INTO @sale_summary (khdm, je, sl)
SELECT 
    model.khdm,
    ROUND(SUM(je), 2) AS je,
    SUM(sl) AS sl 
FROM bsyc_vm_lsxhdmx model
GROUP BY model.khdm;

-- 插入风险汇总数据
INSERT INTO @risk_summary (shop_id, abnormal, risk)
SELECT 
    shop_id,
    SUM(abnormal) AS abnormal,
    SUM(risk) AS risk
FROM (
    SELECT 
        pos.shop_id,
        CASE WHEN COUNT(*) >= 2 AND COUNT(*) <= 3 THEN 1 ELSE 0 END AS abnormal,
        CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS risk
    FROM bsyc_trade_stream model
    INNER JOIN bsyc_shop_pos pos ON model.store_id = pos.store_code
    GROUP BY model.txn_reserved3, pos.shop_id, model.store_id, model.mchnt_cd
) risk_detail
GROUP BY shop_id;

-- 最终查询
SELECT 
    sale.khdm,
    sale.je,
    sale.sl,
    risk.abnormal,
    risk.risk
FROM @sale_summary sale
LEFT JOIN @risk_summary risk ON sale.khdm = risk.shop_id;

-- 优化方案4：使用存储过程优化（推荐用于频繁执行）
CREATE PROCEDURE sp_GetSalesRiskAnalysis_NoCTE
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 创建临时表存储销售汇总
    SELECT 
        model.khdm,
        ROUND(SUM(je), 2) AS je,
        SUM(sl) AS sl 
    INTO #temp_sale_summary
    FROM bsyc_vm_lsxhdmx model WITH (INDEX = IX_bsyc_vm_lsxhdmx_khdm)
    WHERE (@StartDate IS NULL OR model.sale_date >= @StartDate)
      AND (@EndDate IS NULL OR model.sale_date <= @EndDate)
    GROUP BY model.khdm;
    
    -- 创建临时表存储风险计算
    SELECT 
        pos.shop_id,
        CASE WHEN COUNT(*) >= 2 AND COUNT(*) <= 3 THEN 1 ELSE 0 END AS abnormal,
        CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS risk
    INTO #temp_risk_detail
    FROM bsyc_trade_stream model WITH (INDEX = IX_bsyc_trade_stream_store_id)
    INNER JOIN bsyc_shop_pos pos WITH (INDEX = IX_bsyc_shop_pos_store_code) 
        ON model.store_id = pos.store_code
    WHERE (@StartDate IS NULL OR model.transaction_date >= @StartDate)
      AND (@EndDate IS NULL OR model.transaction_date <= @EndDate)
    GROUP BY model.txn_reserved3, pos.shop_id, model.store_id, model.mchnt_cd;
    
    -- 创建临时表存储风险汇总
    SELECT 
        shop_id,
        SUM(abnormal) AS abnormal,
        SUM(risk) AS risk
    INTO #temp_risk_summary
    FROM #temp_risk_detail
    GROUP BY shop_id;
    
    -- 最终查询
    SELECT 
        sale.khdm,
        sale.je,
        sale.sl,
        risk.abnormal,
        risk.risk
    FROM #temp_sale_summary sale
    LEFT JOIN #temp_risk_summary risk ON sale.khdm = risk.shop_id
    ORDER BY sale.je DESC;
    
    -- 清理临时表
    DROP TABLE #temp_sale_summary;
    DROP TABLE #temp_risk_detail;
    DROP TABLE #temp_risk_summary;
END;

-- 优化方案5：使用视图优化（适用于报表查询）
-- 创建销售汇总视图
CREATE VIEW v_SaleSummary AS
SELECT 
    model.khdm,
    ROUND(SUM(je), 2) AS je,
    SUM(sl) AS sl 
FROM bsyc_vm_lsxhdmx model
GROUP BY model.khdm;

-- 创建风险汇总视图
CREATE VIEW v_RiskSummary AS
SELECT 
    shop_id,
    SUM(abnormal) AS abnormal,
    SUM(risk) AS risk
FROM (
    SELECT 
        pos.shop_id,
        CASE WHEN COUNT(*) >= 2 AND COUNT(*) <= 3 THEN 1 ELSE 0 END AS abnormal,
        CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS risk
    FROM bsyc_trade_stream model
    INNER JOIN bsyc_shop_pos pos ON model.store_id = pos.store_code
    GROUP BY model.txn_reserved3, pos.shop_id, model.store_id, model.mchnt_cd
) risk_detail
GROUP BY shop_id;

-- 使用视图的查询
SELECT 
    sale.khdm,
    sale.je,
    sale.sl,
    risk.abnormal,
    risk.risk
FROM v_SaleSummary sale
LEFT JOIN v_RiskSummary risk ON sale.khdm = risk.shop_id;

-- 优化方案6：使用索引视图优化（适用于频繁查询）
CREATE VIEW v_SalesRiskIndexed
WITH SCHEMABINDING
AS
SELECT 
    model.khdm,
    ROUND(SUM(je), 2) AS je,
    SUM(sl) AS sl,
    SUM(CASE WHEN COUNT(*) >= 2 AND COUNT(*) <= 3 THEN 1 ELSE 0 END) AS abnormal,
    SUM(CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END) AS risk
FROM dbo.bsyc_vm_lsxhdmx model
LEFT JOIN dbo.bsyc_trade_stream stream ON model.khdm = stream.shop_id
LEFT JOIN dbo.bsyc_shop_pos pos ON stream.store_id = pos.store_code
GROUP BY model.khdm;

-- 创建唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_v_SalesRiskIndexed_khdm 
ON v_SalesRiskIndexed(khdm);

-- 使用索引视图的查询
SELECT * FROM v_SalesRiskIndexed;

-- 优化方案7：使用分区表优化（适用于大数据量）
-- 如果表数据量很大，建议按时间分区
CREATE PARTITION FUNCTION PF_TimeRange (DATE)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-04-01', '2023-07-01', '2023-10-01', '2024-01-01');

CREATE PARTITION SCHEME PS_TimeRange
AS PARTITION PF_TimeRange TO (FG1, FG2, FG3, FG4, FG5, FG6);

-- 创建分区表
CREATE TABLE bsyc_trade_stream_partitioned (
    transaction_date DATE,
    store_id VARCHAR(50),
    txn_reserved3 VARCHAR(100),
    mchnt_cd VARCHAR(50),
    -- 其他字段...
    CONSTRAINT PK_bsyc_trade_stream_partitioned PRIMARY KEY (transaction_date, store_id)
) ON PS_TimeRange(transaction_date);

-- 使用分区表的查询
SELECT 
    sale.khdm,
    sale.je,
    sale.sl,
    risk.abnormal,
    risk.risk
FROM (
    SELECT 
        model.khdm,
        ROUND(SUM(je), 2) AS je,
        SUM(sl) AS sl 
    FROM bsyc_vm_lsxhdmx model
    GROUP BY model.khdm
) sale
LEFT JOIN (
    SELECT 
        shop_id,
        SUM(abnormal) AS abnormal,
        SUM(risk) AS risk
    FROM (
        SELECT 
            pos.shop_id,
            CASE WHEN COUNT(*) >= 2 AND COUNT(*) <= 3 THEN 1 ELSE 0 END AS abnormal,
            CASE WHEN COUNT(*) > 3 THEN 1 ELSE 0 END AS risk
        FROM bsyc_trade_stream_partitioned model
        INNER JOIN bsyc_shop_pos pos ON model.store_id = pos.store_code
        WHERE model.transaction_date >= '2023-01-01'  -- 分区裁剪
        GROUP BY model.txn_reserved3, pos.shop_id, model.store_id, model.mchnt_cd
    ) risk_detail
    GROUP BY shop_id
) risk ON sale.khdm = risk.shop_id;

-- 性能监控查询
-- 查看查询执行计划
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 查看索引使用情况
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECT_NAME(i.object_id) IN ('bsyc_vm_lsxhdmx', 'bsyc_trade_stream', 'bsyc_shop_pos')
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;

-- 建议的索引创建语句（不使用CTE的情况下更重要）
-- 为bsyc_vm_lsxhdmx表创建索引
CREATE INDEX IX_bsyc_vm_lsxhdmx_khdm ON bsyc_vm_lsxhdmx(khdm);
CREATE INDEX IX_bsyc_vm_lsxhdmx_khdm_je_sl ON bsyc_vm_lsxhdmx(khdm) INCLUDE (je, sl);

-- 为bsyc_trade_stream表创建索引
CREATE INDEX IX_bsyc_trade_stream_store_id ON bsyc_trade_stream(store_id);
CREATE INDEX IX_bsyc_trade_stream_txn_reserved3 ON bsyc_trade_stream(txn_reserved3);
CREATE INDEX IX_bsyc_trade_stream_composite ON bsyc_trade_stream(store_id, txn_reserved3, mchnt_cd);

-- 为bsyc_shop_pos表创建索引
CREATE INDEX IX_bsyc_shop_pos_store_code ON bsyc_shop_pos(store_code);
CREATE INDEX IX_bsyc_shop_pos_shop_id ON bsyc_shop_pos(shop_id); 